{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1bbe66be",
   "metadata": {},
   "source": [
    "Pandas详解\n",
    "1. 业务理解\n",
    "2. 数据加载(*)--> DataFrame\n",
    "3. 数据准备(*)-->拼接、合并、筛选、清洗、预处理\n",
    "4. 数据透视(*)\n",
    "5. 数据呈现(*)\n",
    "6. 业务洞察"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a797e70b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "plt.rcParams['font.sans-serif'].insert(0,'simHei')\n",
    "plt.rcParams['axes.unicode_minus']=False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "45c2f13e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>出生年月</th>\n",
       "      <th>单位名称</th>\n",
       "      <th>积分分值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>公示编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>202300001</th>\n",
       "      <td>张浩</td>\n",
       "      <td>1977-02</td>\n",
       "      <td>北京首钢股份有限公司</td>\n",
       "      <td>140.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202300002</th>\n",
       "      <td>冯云</td>\n",
       "      <td>1982-02</td>\n",
       "      <td>中国人民解放军空军二十三厂</td>\n",
       "      <td>134.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202300003</th>\n",
       "      <td>王天东</td>\n",
       "      <td>1975-01</td>\n",
       "      <td>中建二局第三建筑工程有限公司</td>\n",
       "      <td>133.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202300004</th>\n",
       "      <td>陈军</td>\n",
       "      <td>1976-07</td>\n",
       "      <td>中建二局第三建筑工程有限公司</td>\n",
       "      <td>133.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202300005</th>\n",
       "      <td>樊海瑞</td>\n",
       "      <td>1981-06</td>\n",
       "      <td>中国民生银行股份有限公司</td>\n",
       "      <td>132.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202305999</th>\n",
       "      <td>曹恰</td>\n",
       "      <td>1983-09</td>\n",
       "      <td>首都师范大学科德学院</td>\n",
       "      <td>109.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202306000</th>\n",
       "      <td>罗佳</td>\n",
       "      <td>1981-05</td>\n",
       "      <td>厦门方胜众合企业服务有限公司海淀分公司</td>\n",
       "      <td>109.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202306001</th>\n",
       "      <td>席盛代</td>\n",
       "      <td>1983-06</td>\n",
       "      <td>中国华能集团清洁能源技术研究院有限公司</td>\n",
       "      <td>109.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202306002</th>\n",
       "      <td>彭芸芸</td>\n",
       "      <td>1981-09</td>\n",
       "      <td>北京汉杰凯德文化传播有限公司</td>\n",
       "      <td>109.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202306003</th>\n",
       "      <td>张越</td>\n",
       "      <td>1982-01</td>\n",
       "      <td>大爱城投资控股有限公司</td>\n",
       "      <td>109.92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6003 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            姓名     出生年月                 单位名称    积分分值\n",
       "公示编号                                                \n",
       "202300001   张浩  1977-02           北京首钢股份有限公司  140.05\n",
       "202300002   冯云  1982-02        中国人民解放军空军二十三厂  134.29\n",
       "202300003  王天东  1975-01       中建二局第三建筑工程有限公司  133.63\n",
       "202300004   陈军  1976-07       中建二局第三建筑工程有限公司  133.29\n",
       "202300005  樊海瑞  1981-06         中国民生银行股份有限公司  132.46\n",
       "...        ...      ...                  ...     ...\n",
       "202305999   曹恰  1983-09           首都师范大学科德学院  109.92\n",
       "202306000   罗佳  1981-05  厦门方胜众合企业服务有限公司海淀分公司  109.92\n",
       "202306001  席盛代  1983-06  中国华能集团清洁能源技术研究院有限公司  109.92\n",
       "202306002  彭芸芸  1981-09       北京汉杰凯德文化传播有限公司  109.92\n",
       "202306003   张越  1982-01          大爱城投资控股有限公司  109.92\n",
       "\n",
       "[6003 rows x 4 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#从csv文件加载数据创建DataFrame\n",
    "df3=pd.read_csv(\n",
    "    'data/2023年北京积分落户数据.csv',\n",
    "    # encoding='utf-8', #字符串                         #字符集\n",
    "    # sep='\\t'                                         #字段的分隔符（默认是逗号）\n",
    "    index_col='公示编号',                               #指定充当行索引的列\n",
    "    # usercols=['公示编号','姓名','积分分值']             #指定需要加载的列\n",
    "    # nrows=10,                                         #指定加载多少行\n",
    "    # skiprows=np.arange(1,21)                          #跳过哪些行\n",
    "    # true_values=['是','Y','yes','Yes'],               #哪些值视为布尔值True\n",
    "    # false_values=['否','N','no','No'],                #哪些值视为布尔值False\n",
    "    # na_values=['---','N/A']                           #哪些值视为空值\n",
    "    \n",
    ")\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e4ba872a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 6003 entries, 202300001 to 202306003\n",
      "Data columns (total 4 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   姓名      6003 non-null   object \n",
      " 1   出生年月    6003 non-null   object \n",
      " 2   单位名称    6003 non-null   object \n",
      " 3   积分分值    6003 non-null   float64\n",
      "dtypes: float64(1), object(3)\n",
      "memory usage: 234.5+ KB\n"
     ]
    }
   ],
   "source": [
    "#查看DataFrame信息\n",
    "df3.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "742405b8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>出生年月</th>\n",
       "      <th>单位名称</th>\n",
       "      <th>积分分值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>公示编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>202300001</th>\n",
       "      <td>张浩</td>\n",
       "      <td>1977-02</td>\n",
       "      <td>北京首钢股份有限公司</td>\n",
       "      <td>140.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202300002</th>\n",
       "      <td>冯云</td>\n",
       "      <td>1982-02</td>\n",
       "      <td>中国人民解放军空军二十三厂</td>\n",
       "      <td>134.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202300003</th>\n",
       "      <td>王天东</td>\n",
       "      <td>1975-01</td>\n",
       "      <td>中建二局第三建筑工程有限公司</td>\n",
       "      <td>133.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            姓名     出生年月            单位名称    积分分值\n",
       "公示编号                                           \n",
       "202300001   张浩  1977-02      北京首钢股份有限公司  140.05\n",
       "202300002   冯云  1982-02   中国人民解放军空军二十三厂  134.29\n",
       "202300003  王天东  1975-01  中建二局第三建筑工程有限公司  133.63"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看DataFrame前N行\n",
    "df3.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7c3fee38",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>出生年月</th>\n",
       "      <th>单位名称</th>\n",
       "      <th>积分分值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>公示编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>202306001</th>\n",
       "      <td>席盛代</td>\n",
       "      <td>1983-06</td>\n",
       "      <td>中国华能集团清洁能源技术研究院有限公司</td>\n",
       "      <td>109.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202306002</th>\n",
       "      <td>彭芸芸</td>\n",
       "      <td>1981-09</td>\n",
       "      <td>北京汉杰凯德文化传播有限公司</td>\n",
       "      <td>109.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202306003</th>\n",
       "      <td>张越</td>\n",
       "      <td>1982-01</td>\n",
       "      <td>大爱城投资控股有限公司</td>\n",
       "      <td>109.92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            姓名     出生年月                 单位名称    积分分值\n",
       "公示编号                                                \n",
       "202306001  席盛代  1983-06  中国华能集团清洁能源技术研究院有限公司  109.92\n",
       "202306002  彭芸芸  1981-09       北京汉杰凯德文化传播有限公司  109.92\n",
       "202306003   张越  1982-01          大爱城投资控股有限公司  109.92"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看DataFrame后N行\n",
    "df3.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "89480369",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>Close</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-12-30</th>\n",
       "      <td>55.8700</td>\n",
       "      <td>56.1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-29</th>\n",
       "      <td>56.1400</td>\n",
       "      <td>57.0900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-28</th>\n",
       "      <td>58.3500</td>\n",
       "      <td>56.1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-27</th>\n",
       "      <td>57.4900</td>\n",
       "      <td>58.8000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-23</th>\n",
       "      <td>57.8500</td>\n",
       "      <td>56.4400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-07</th>\n",
       "      <td>66.7337</td>\n",
       "      <td>66.5873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-06</th>\n",
       "      <td>63.6697</td>\n",
       "      <td>65.1627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-05</th>\n",
       "      <td>62.0304</td>\n",
       "      <td>61.4937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-04</th>\n",
       "      <td>66.0604</td>\n",
       "      <td>62.6452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-03</th>\n",
       "      <td>67.2313</td>\n",
       "      <td>66.6751</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>251 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Open    Close\n",
       "Date                        \n",
       "2022-12-30  55.8700  56.1300\n",
       "2022-12-29  56.1400  57.0900\n",
       "2022-12-28  58.3500  56.1500\n",
       "2022-12-27  57.4900  58.8000\n",
       "2022-12-23  57.8500  56.4400\n",
       "...             ...      ...\n",
       "2022-01-07  66.7337  66.5873\n",
       "2022-01-06  63.6697  65.1627\n",
       "2022-01-05  62.0304  61.4937\n",
       "2022-01-04  66.0604  62.6452\n",
       "2022-01-03  67.2313  66.6751\n",
       "\n",
       "[251 rows x 2 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#从Excel文件中加载数据创建DataFrame\n",
    "df4 = pd.read_excel(\n",
    "    'data/2022年股票数据.xlsx',\n",
    "    sheet_name='JD',        #加载的工作表的名字\n",
    "    usecols=['Date','Open','Close'],\n",
    "    index_col='Date'\n",
    ")\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c70c015",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "Name: A, dtype: int64\n",
      "1    1\n",
      "2    2\n",
      "3    3\n",
      "4    4\n",
      "Name: A, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#Pandas数据结构--Series\n",
    "import pandas as pd\n",
    "\n",
    "#创建一个Series对象，指定名称为'A'，值分别为1，2，3，4\n",
    "#默认索引为0，1，2，3\n",
    "series=pd.Series([1,2,3,4],name='A')\n",
    "\n",
    "#显示Series对象\n",
    "print(series)\n",
    "\n",
    "#如果你想要显示地设置索引，可以这样做：\n",
    "custom_index=[1,2,3,4]      #自定义索引\n",
    "series_with_index=pd.Series([1,2,3,4],index=custom_index,name='A')\n",
    "\n",
    "#显示带有自定义索引的Series对象\n",
    "print(series_with_index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "5b999b92",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "#创建一个简单的DataFrame\n",
    "df=pd.DataFrame({\n",
    "'Name':['Alice','Bob','Charlie'],\n",
    "'Age':[25,30,35],\n",
    "'City':['New York','Los Angeles','Chicago']\n",
    "})\n",
    "\n",
    "#将DataFrame写入Excel文件，写入'sheet1’表单\n",
    "df.to_excel('output.xlsx',sheet_name='Sheet1',index=False)\n",
    "\n",
    "#写入多个表单，使用ExcelWriter\n",
    "with pd.ExcelWriter('output.xlsx') as writer:\n",
    "    df.to_excel(writer,sheet_name='Sheet1',index=False)\n",
    "    df.to_excel(writer,sheet_name='Sheet2',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48a1b2eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Pandas数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "623d95c7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0      3\n",
      "1      3\n",
      "2    NaN\n",
      "3      1\n",
      "4      3\n",
      "5    NaN\n",
      "6      2\n",
      "7      1\n",
      "8     na\n",
      "Name: NUM_BEDROOMS, dtype: object\n",
      "0    False\n",
      "1    False\n",
      "2     True\n",
      "3    False\n",
      "4    False\n",
      "5     True\n",
      "6    False\n",
      "7    False\n",
      "8    False\n",
      "Name: NUM_BEDROOMS, dtype: bool\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('data/property-data.csv')\n",
    "\n",
    "print(df['NUM_BEDROOMS'])\n",
    "print(df['NUM_BEDROOMS'].isnull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "008e8393",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID  ST_NUM    ST_NAME OWN_OCCUPIED NUM_BEDROOMS NUM_BATH SQ_FT\n",
      "0  100001000.0   104.0     PUTNAM            Y            3        1  1000\n",
      "1  100002000.0   197.0  LEXINGTON            N            3      1.5    --\n",
      "8  100009000.0   215.0    TREMONT            Y           na        2  1800\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('data/property-data.csv')\n",
    "\n",
    "new_df=df.dropna()\n",
    "\n",
    "print(new_df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "95956300",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID  ST_NUM     ST_NAME OWN_OCCUPIED NUM_BEDROOMS NUM_BATH SQ_FT\n",
      "0  100001000.0   104.0      PUTNAM            Y            3        1  1000\n",
      "1  100002000.0   197.0   LEXINGTON            N            3      1.5    --\n",
      "2  100003000.0     NaN   LEXINGTON            N          NaN        1   850\n",
      "3  100004000.0   201.0    BERKELEY           12            1      NaN   700\n",
      "5  100006000.0   207.0    BERKELEY            Y          NaN        1   800\n",
      "6  100007000.0     NaN  WASHINGTON          NaN            2   HURLEY   950\n",
      "7  100008000.0   213.0     TREMONT            Y            1        1   NaN\n",
      "8  100009000.0   215.0     TREMONT            Y           na        2  1800\n"
     ]
    }
   ],
   "source": [
    "#去除空值\n",
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('data/property-data.csv')\n",
    "\n",
    "#df.dropna(subset=['ST_NUM'],inplace=True)\n",
    "df.dropna(subset=['PID'],inplace=True)\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2a885f9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID   ST_NUM     ST_NAME OWN_OCCUPIED NUM_BEDROOMS NUM_BATH  SQ_FT\n",
      "0  100001000.0    104.0      PUTNAM            Y            3        1   1000\n",
      "1  100002000.0    197.0   LEXINGTON            N            3      1.5     --\n",
      "2  100003000.0  12345.0   LEXINGTON            N        12345        1    850\n",
      "3  100004000.0    201.0    BERKELEY           12            1    12345    700\n",
      "4      12345.0    203.0    BERKELEY            Y            3        2   1600\n",
      "5  100006000.0    207.0    BERKELEY            Y        12345        1    800\n",
      "6  100007000.0  12345.0  WASHINGTON        12345            2   HURLEY    950\n",
      "7  100008000.0    213.0     TREMONT            Y            1        1  12345\n",
      "8  100009000.0    215.0     TREMONT            Y           na        2   1800\n"
     ]
    }
   ],
   "source": [
    "#填充为空的字段\n",
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('data/property-data.csv')\n",
    "\n",
    "df.fillna(12345,inplace=True)\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b881922d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID  ST_NUM     ST_NAME OWN_OCCUPIED NUM_BEDROOMS NUM_BATH SQ_FT\n",
      "0  100001000.0   104.0      PUTNAM            Y            3        1  1000\n",
      "1  100002000.0   197.0   LEXINGTON            N            3      1.5    --\n",
      "2  100003000.0     NaN   LEXINGTON            N          NaN        1   850\n",
      "3  100004000.0   201.0    BERKELEY           12            1      NaN   700\n",
      "4      12345.0   203.0    BERKELEY            Y            3        2  1600\n",
      "5  100006000.0   207.0    BERKELEY            Y          NaN        1   800\n",
      "6  100007000.0     NaN  WASHINGTON          NaN            2   HURLEY   950\n",
      "7  100008000.0   213.0     TREMONT            Y            1        1   NaN\n",
      "8  100009000.0   215.0     TREMONT            Y           na        2  1800\n"
     ]
    }
   ],
   "source": [
    "#填充指定列PID为空的字段\n",
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('data/property-data.csv')\n",
    "\n",
    "#df['PID'].fillna(12345,inplace=True)\n",
    "df.fillna({'PID':12345},inplace=True)\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ae3ac6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID      ST_NUM     ST_NAME OWN_OCCUPIED NUM_BEDROOMS NUM_BATH SQ_FT\n",
      "0  100001000.0  104.000000      PUTNAM            Y            3        1  1000\n",
      "1  100002000.0  197.000000   LEXINGTON            N            3      1.5    --\n",
      "2  100003000.0  191.428571   LEXINGTON            N          NaN        1   850\n",
      "3  100004000.0  201.000000    BERKELEY           12            1      NaN   700\n",
      "4          NaN  203.000000    BERKELEY            Y            3        2  1600\n",
      "5  100006000.0  207.000000    BERKELEY            Y          NaN        1   800\n",
      "6  100007000.0  191.428571  WASHINGTON          NaN            2   HURLEY   950\n",
      "7  100008000.0  213.000000     TREMONT            Y            1        1   NaN\n",
      "8  100009000.0  215.000000     TREMONT            Y           na        2  1800\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\徐钦慧\\AppData\\Local\\Temp\\ipykernel_5320\\347846678.py:8: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  df[\"ST_NUM\"].fillna(x,inplace=True)\n"
     ]
    }
   ],
   "source": [
    "#使用mean()方法计算列的均值替换空值\n",
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('data/property-data.csv')\n",
    "\n",
    "x=df[\"ST_NUM\"].mean()\n",
    "\n",
    "df[\"ST_NUM\"].fillna(x,inplace=True)\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65a87e71",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID  ST_NUM     ST_NAME OWN_OCCUPIED NUM_BEDROOMS NUM_BATH SQ_FT\n",
      "0  100001000.0   104.0      PUTNAM            Y            3        1  1000\n",
      "1  100002000.0   197.0   LEXINGTON            N            3      1.5    --\n",
      "2  100003000.0   203.0   LEXINGTON            N          NaN        1   850\n",
      "3  100004000.0   201.0    BERKELEY           12            1      NaN   700\n",
      "4          NaN   203.0    BERKELEY            Y            3        2  1600\n",
      "5  100006000.0   207.0    BERKELEY            Y          NaN        1   800\n",
      "6  100007000.0   203.0  WASHINGTON          NaN            2   HURLEY   950\n",
      "7  100008000.0   213.0     TREMONT            Y            1        1   NaN\n",
      "8  100009000.0   215.0     TREMONT            Y           na        2  1800\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\徐钦慧\\AppData\\Local\\Temp\\ipykernel_5320\\1219126341.py:8: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  df[\"ST_NUM\"].fillna(x,inplace=True)\n"
     ]
    }
   ],
   "source": [
    "#使用median()方法计算列的中位数替换空值\n",
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('data/property-data.csv')\n",
    "\n",
    "x=df[\"ST_NUM\"].median()\n",
    "\n",
    "df[\"ST_NUM\"].fillna(x,inplace=True)\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "e3e5f25c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID  ST_NUM     ST_NAME OWN_OCCUPIED NUM_BEDROOMS NUM_BATH SQ_FT\n",
      "0  100001000.0   104.0      PUTNAM            Y            3        1  1000\n",
      "1  100002000.0   197.0   LEXINGTON            N            3      1.5    --\n",
      "2  100003000.0   201.0   LEXINGTON            N          NaN        1   850\n",
      "3  100004000.0   201.0    BERKELEY           12            1      NaN   700\n",
      "4          NaN   203.0    BERKELEY            Y            3        2  1600\n",
      "5  100006000.0   207.0    BERKELEY            Y          NaN        1   800\n",
      "6  100007000.0   215.0  WASHINGTON          NaN            2   HURLEY   950\n",
      "7  100008000.0   213.0     TREMONT            Y            1        1   NaN\n",
      "8  100009000.0   215.0     TREMONT            Y           na        2  1800\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\徐钦慧\\AppData\\Local\\Temp\\ipykernel_5320\\3708830842.py:8: FutureWarning: A value is trying to be set on a copy of a DataFrame or Series through chained assignment using an inplace method.\n",
      "The behavior will change in pandas 3.0. This inplace method will never work because the intermediate object on which we are setting values always behaves as a copy.\n",
      "\n",
      "For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' or df[col] = df[col].method(value) instead, to perform the operation inplace on the original object.\n",
      "\n",
      "\n",
      "  df[\"ST_NUM\"].fillna(x,inplace=True)\n"
     ]
    }
   ],
   "source": [
    "#使用mode()方法计算列的众数替换空值\n",
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('data/property-data.csv')\n",
    "\n",
    "x=df[\"ST_NUM\"].mode()\n",
    "\n",
    "df[\"ST_NUM\"].fillna(x,inplace=True)\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6746143a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     name  age\n",
      "0  Google   50\n",
      "1  Runoob  120\n",
      "2  Taobao  120\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "person={\n",
    "    \"name\":['Google','Runoob','Taobao'],\n",
    "    \"age\":[50,200,12345]\n",
    "}\n",
    "\n",
    "df=pd.DataFrame(person)\n",
    "\n",
    "for x in df.index:\n",
    "    if df.loc[x,\"age\"]>120:\n",
    "        df.loc[x,\"age\"]=120\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e3e3e509",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     name  age\n",
      "0  Google   50\n",
      "1  Runoob   40\n",
      "3  Taobao   23\n"
     ]
    }
   ],
   "source": [
    "#Pandas清洗重复数据\n",
    "import pandas as pd\n",
    "\n",
    "person={\n",
    "    \"name\":['Google','Runoob','Runoob','Taobao'],\n",
    "    \"age\":[50,40,40,23]\n",
    "}\n",
    "\n",
    "df=pd.DataFrame(person)\n",
    "\n",
    "df.drop_duplicates(inplace=True)\n",
    "\n",
    "print(df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Project",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
